package com.vasthu.core.dao;

import com.vasthu.api.dao.ItemDAO;
import com.vasthu.api.domain.Item;
import com.vasthu.utility.VstDBManager;
import java.util.*;
import java.sql.*;
import org.apache.log4j.Logger;

public class ItemDAOImpl implements ItemDAO {

    static Logger logger = Logger.getLogger(ItemDAOImpl.class);

    public boolean insertItem(Item item) {
        if (item == null) {
            return false;
        }

        PreparedStatement pst = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("INSERT INTO Item(item_code, item_name, item_desc, item_brand, item_cat_id, status) VALUES(?, ?, ?, ?, ?, 1)");

            pst.setString(1, item.getItemCode());
            pst.setString(2, item.getItemName());
            pst.setString(3, item.getItemDesc());
            pst.setString(4, item.getItemBrand());
            pst.setLong(5, item.getItemCategoryId());

            pst.executeUpdate();

        } catch (Exception ex) {
            System.out.println("ERROR : ItemDAOImpl->insertItem()");
        } finally {
            VstDBManager.get().close();
        }

        return true;
    }

    public Item getItemById(long itemId) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Item item = new Item();
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("SELECT * FROM Item WHERE item_id = ? AND status = 1");
            pst.setLong(1, itemId);
            rs = pst.executeQuery();

            while (rs.next()) {
                item.setItemId(rs.getInt("item_id"));
                item.setItemCode(rs.getString("item_code"));
                item.setItemName(rs.getString("item_name"));
                item.setItemDesc(rs.getString("item_desc"));
                item.setItemBrand(rs.getString("item_brand"));
                item.setItemCategoryId(rs.getLong("item_cat_id"));
                break;
            }

        } catch (Exception ex) {
            System.out.println("ERROR : ItemDAOImpl->deleteItemById()");
        } finally {
            VstDBManager.get().close();
        }

        return item;
    }

    public Item getItemByCode(String itemCode) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Item item = new Item();
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("SELECT * FROM Item WHERE item_code = ? AND status = 1");
            pst.setString(1, itemCode);
            rs = pst.executeQuery();

            while (rs.next()) {
                item.setItemId(rs.getInt("item_id"));
                item.setItemCode(rs.getString("item_code"));
                item.setItemName(rs.getString("item_name"));
                item.setItemDesc(rs.getString("item_desc"));
                item.setItemBrand(rs.getString("item_brand"));
                item.setItemCategoryId(rs.getLong("item_cat_id"));
                break;
            }

        } catch (Exception ex) {
            System.out.println("ERROR : ItemDAOImpl->deleteItemByName()");
        } finally {
            VstDBManager.get().close();
        }

        return item;
    }

    public boolean deleteItemById(long itemId) {
        PreparedStatement pst = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("DELETE FROM Item WHERE item_id = ?");
            pst.setLong(1, itemId);
            pst.executeUpdate();
        } catch (Exception ex) {
            System.out.println("ERROR : ItemDAOImpl->deleteItemById()");
        } finally {
            VstDBManager.get().close();
        }
        return true;
    }

    public List<Item> getAllItems() {
        List<Item> itemList = new ArrayList<Item>();
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("SELECT * FROM Item WHERE status = 1");
            rs = pst.executeQuery();

            while (rs.next()) {
                Item item = new Item();

                item.setItemId(rs.getInt("item_id"));
                item.setItemCode(rs.getString("item_code"));
                item.setItemName(rs.getString("item_name"));
                item.setItemDesc(rs.getString("item_desc"));
                item.setItemBrand(rs.getString("item_brand"));
                item.setItemCategoryId(rs.getLong("item_cat_id"));

                itemList.add(item);
            }
        } catch (SQLException ex) {
            System.out.println("ERROR : ItemDAOImpl->getAllItems()");
        } finally {
            VstDBManager.get().close();
        }

        return itemList;
    }
}